package org.homly.service;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import org.homly.model.Scheme;
import org.homly.sql.SQLTool;
import org.homly.tool.StringTool;

public class SchemeService {

	@SuppressWarnings("unchecked")
	public static List<HashMap<String, String>> getSchemes(HashMap para) throws Exception {

		List<HashMap<String, String>> schemes = null;

		Short isRecommend = (Short) para.get("isRecommend");
		Short status = (Short) para.get("status");

		StringBuffer sqlSB = new StringBuffer();
		sqlSB.append(" SELECT * FROM scheme WHERE 1=1");

		if (isRecommend != null && "0".equals(isRecommend.toString())) {
			sqlSB.append(" AND scheme_is_recommend=0");
		} else if (isRecommend != null && "1".equals(isRecommend.toString())) {
			sqlSB.append(" AND scheme_is_recommend=1");
		}
		if (status != null && "0".equals(status.toString())) {
			sqlSB.append(" AND scheme_status=0");
		} else if (status != null && "1".equals(status.toString())) {
			sqlSB.append(" AND scheme_status=1");
		}

		sqlSB.append(" ORDER BY scheme_sort_no DESC");
		try {
			schemes = SQLTool.query(sqlSB.toString());
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return schemes;
	}

	public static int getSchemeTopSortNo() throws Exception {

		int topSortNo = 0;

		String sql = " SELECT (MAX(scheme_sort_no)+1) AS top_sort_no FROM scheme";
		List<HashMap<String, String>> result = SQLTool.query(sql);
		if (result.size() > 0) {
			String topSortNoS = result.get(0).get("top_sort_no");
			if (topSortNoS != null && StringTool.isNumber(topSortNoS)) {
				topSortNo = Integer.valueOf(topSortNoS);
			}
		}

		return topSortNo;
	}

	public static int getSchemeBottomSortNo() throws Exception {

		int bottomSortNo = 0;

		String sql = " SELECT (MIN(scheme_sort_no)-1) AS bottom_sort_no FROM scheme";
		List<HashMap<String, String>> result = SQLTool.query(sql);
		if (result.size() > 0) {
			String bottomSortNoS = result.get(0).get("bottom_sort_no");
			if (bottomSortNoS != null && StringTool.isNumber(bottomSortNoS)) {
				bottomSortNo = Integer.valueOf(bottomSortNoS);
			}
		}

		return bottomSortNo;
	}

	@SuppressWarnings("unchecked")
	public static HashMap<String, String> getScheme(HashMap para) throws Exception {

		HashMap<String, String> scheme = null;

		Integer id = (Integer) para.get("id");
		Short status = (Short) para.get("status");

		StringBuffer sqlSB = new StringBuffer();
		sqlSB.append(" SELECT * FROM scheme WHERE 1=1");

		if (id != null) {
			sqlSB.append(" AND scheme_id=").append(id);
		}
		if (status != null) {
			sqlSB.append(" AND scheme_status=").append(status);
		}

		sqlSB.append(" LIMIT 1");
		try {
			List<HashMap<String, String>> schemes = null;
			schemes = SQLTool.query(sqlSB.toString());
			if (schemes != null && schemes.size() > 0) {
				scheme = schemes.get(0);
			} else {
				scheme = new HashMap();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return scheme;
	}

	public static void updateScheme(Scheme scheme) throws Exception {

		String sql = " UPDATE scheme SET scheme_name=?,scheme_is_recommend=?,scheme_note=?,scheme_status=?,scheme_modify_time=? WHERE scheme_id=?";
		Object[] para = {scheme.getSchemeName(), scheme.getSchemeIsRecommend(), scheme.getSchemeNote(), scheme.getSchemeStatus(),
				new Date(), scheme.getSchemeId()};

		SQLTool.update(sql, para);
	}

	public static void setSchemeTop(Integer schemeId) throws Exception {

		String sql = " UPDATE scheme SET scheme_sort_no=? WHERE scheme_id=?";
		Object[] para = {getSchemeTopSortNo(), schemeId};

		SQLTool.update(sql, para);
	}

	public static void setSchemeBottom(Integer schemeId) throws Exception {

		String sql = " UPDATE scheme SET scheme_sort_no=? WHERE scheme_id=?";
		Object[] para = {getSchemeBottomSortNo(), schemeId};

		SQLTool.update(sql, para);
	}

	public static List<HashMap<String, String>> getSchemeDetailsBySchemeId(
			Integer schemeId) {

		List<HashMap<String, String>> schemeDetails = null;

		String sql = " SELECT * FROM scheme_detail WHERE scheme_id=" + schemeId;
		try {
			schemeDetails = SQLTool.query(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			schemeDetails = new ArrayList<HashMap<String, String>>();
		}

		return schemeDetails;
	}
}
